package com.liang.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.liang.shortlink.project.dao.entity.LinkAccessLogsDO;
import com.liang.shortlink.project.dao.entity.LinkAccessStatsDO;
import com.liang.shortlink.project.dto.req.ShortLinkGroupStatsAccessRecordReqDTO;
import com.liang.shortlink.project.dto.req.ShortLinkGroupStatsReqDTO;
import com.liang.shortlink.project.dto.req.ShortLinkStatsReqDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 访问日志监控持久层
 */
public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogsDO> {

    /**
     * 根据短链接获取指定日期内高频访问IP数
     */
    @Select("""
            SELECT tlal.ip,
                   COUNT(tlal.ip) AS count
            FROM t_link tl
                     INNER JOIN
                 t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
            WHERE tlal.full_short_url = #{param.fullShortUrl}
              AND tl.gid = #{param.gid}
              AND tl.del_flag = '0'
              AND tl.enable_status = #{param.enableStatus}
              AND tlal.create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY tlal.full_short_url, tl.gid, tlal.ip
            ORDER BY count DESC
            LIMIT 5;
            """)
    List<HashMap<String, Object>> listTopIpStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据短链接获取指定日期内新旧访客数据
     */
    @Select("""
            SELECT SUM(old_user) AS oldUserCnt,
                   SUM(new_user) AS newUserCnt
            FROM (SELECT IF(COUNT(DISTINCT DATE(tlal.create_time)) > 1, 1, 0)          AS old_user,
                         IF(COUNT(DISTINCT DATE(tlal.create_time)) = 1
                                AND MAX(tlal.create_time) >= #{param.startDate}
                                AND MAX(tlal.create_time) <= #{param.endDate}, 1, 0)   AS new_user
                  FROM t_link tl
                           INNER JOIN t_link_access_logs tlal
                                      ON tl.full_short_url = tlal.full_short_url
                  WHERE tlal.full_short_url = #{param.fullShortUrl}
                    AND tl.gid = #{param.gid}
                    AND tl.enable_status = #{param.enableStatus}
                    AND tl.del_flag = '0'
                  GROUP BY tlal.user)
            AS user_counts;
            """)
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 判断用户新老访客
     */
    @Select("""
            <script>
                SELECT tlal.user,
                    IF(MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate}, '新访客', '老访客') AS uvType
                FROM t_link tl
                      INNER JOIN
                  t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
                WHERE tlal.full_short_url = #{fullShortUrl}
                AND tl.gid = #{gid}
                AND tl.del_flag = '0'
                AND tl.enable_status = #{enableStatus}
                AND tlal.user IN
                    <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'>
                        #{item}
                    </foreach>
                GROUP BY tlal.user;
            </script>
            """)
    List<Map<String, Object>> selectUvTypeByUsers(
            @Param("gid") String gid,
            @Param("fullShortUrl") String fullShortUrl,
            @Param("enableStatus") Integer enableStatus,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );

    /**
     * 判断分组用户新老访客
     */
    @Select("""
            <script>
                SELECT tlal.user,
                    IF(MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate}, '新访客', '老访客') AS uvType
                FROM t_link tl
                    INNER JOIN
                        t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
                WHERE tl.gid = #{gid}
                    AND tl.del_flag = '0'
                    AND tl.enable_status = '0'
                    AND tlal.user IN
                        <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'>
                            #{item}
                        </foreach>
                GROUP BY tlal.user;
            </script>
            """)
    List<Map<String, Object>> selectGroupUvTypeByUsers(
            @Param("gid") String gid,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );

    /**
     * 根据短链接获取指定日期内PV、UV、UIP数据
     */
    @Select("""
            SELECT COUNT(tlal.user)          AS pv,
                   COUNT(DISTINCT tlal.user) AS uv,
                   COUNT(DISTINCT tlal.ip)   AS uip
            FROM t_link tl
                     INNER JOIN
                 t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
            WHERE tlal.full_short_url = #{param.fullShortUrl}
              AND tl.gid = #{param.gid}
              AND tl.del_flag = '0'
              AND tl.enable_status = #{param.enableStatus}
              AND tlal.create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY tlal.full_short_url, tl.gid;
            """)
    LinkAccessStatsDO findPvUvUipStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据分组获取指定日期内PV、UV、UIP数据
     */
    @Select("""
            SELECT COUNT(tlal.user)          AS pv,
                   COUNT(DISTINCT tlal.user) AS uv,
                   COUNT(DISTINCT tlal.ip)   AS uip
            FROM t_link tl
                     INNER JOIN
                 t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
            WHERE tl.gid = #{param.gid}
              AND tl.del_flag = '0'
              AND tl.enable_status = '0'
              AND tlal.create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY tl.gid;
            """)
    LinkAccessStatsDO findPvUvUipStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    /**
     * 根据分组获取指定日期内高频访问IP数据
     */
    @Select("""
            SELECT tlal.ip,
                   COUNT(tlal.ip) as count
            FROM t_link tl
                     INNER JOIN
                 t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url
            WHERE tl.gid = #{param.gid}
              AND tl.del_flag = '0'
              AND tl.enable_status = '0'
              AND tlal.create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            GROUP BY tl.gid, tlal.ip
            ORDER BY count DESC
            LIMIT 5;
            """)
    List<HashMap<String, Object>> listTopIpStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    @Select("""
            SELECT tlal.*
            FROM t_link tl
                     INNER JOIN t_link_access_logs tlal
                         ON tl.full_short_url = tlal.full_short_url
            WHERE tl.gid = #{param.gid}
              AND tl.del_flag = '0'
              AND tl.enable_status = '0'
              AND tlal.create_time BETWEEN CONCAT(#{param.startDate}, ' 00:00:00') AND CONCAT(#{param.endDate}, ' 23:59:59')
            ORDER BY tlal.create_time DESC
            """)
    IPage<LinkAccessLogsDO> selectGroupPage(@Param("param") ShortLinkGroupStatsAccessRecordReqDTO requestParam);
}
